package cn.ac.iscas.utils;

import cn.ac.iscas.domain.source.DamengSourceColumn;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

public class DamengSqlUtils {

    private final static String IS_CONSTANT_COLUMN = "isConstantCol";
    private final static String CONSTANT_VALUE = "constantVal";
    private final static String FRONT_TABLE_NAME = "srcTableName";
    private final static String TARGET_TABLE_NAME = "tgtTableName";

    public static String getCreateTemporaryTableSql(String tableName, List<DamengSourceColumn> columns) {
        String createTempTableHeader = String.format("CREATE GLOBAL TEMPORARY TABLE %s ( ", tableName);
        StringBuffer createTempTableBuf = new StringBuffer(createTempTableHeader);
        List<String> primaryKeys = new ArrayList<>();
        for (DamengSourceColumn column : columns) {
            String colName = column.getName();
            String colType = column.getType();
            if (colType.equalsIgnoreCase("varchar") || colType.equalsIgnoreCase("char")) {
                colType = String.format(" %s ( %s ) ", column.getType(), column.getCharMaxLen());
            }
            String colNotNul = column.getNullable() ? "" : "NOT NULL";
            if (column.isPrimaryKey()) {
                primaryKeys.add(colName);
            }

            createTempTableBuf.append(String.format(" \"%s\" %s %s , ", colName, colType, colNotNul));
        }

        String primaryKeysStr = String.format("NOT CLUSTER PRIMARY KEY(%s)", primaryKeys.stream().map(s -> "\"" + s + "\"").collect(Collectors.joining(", ")));
        createTempTableBuf.append(primaryKeysStr).append(");");
        return createTempTableBuf.toString();
    }

    public static String getColumSql(List<DamengSourceColumn> columns) {
        List<String> columnInOrder = new ArrayList<>();
        for (DamengSourceColumn column : columns) {
            columnInOrder.add(column.getName());
        }

        return String.join(",", columnInOrder);
    }

    public static String getSelectAndStaticSql(List<Map<String, String>> columns, String frontTableName, String targetTableName) {
        String conditionSql = " where 1 = 1";

        StringBuffer sqlBuffer = new StringBuffer("SELECT ");
        for (Map<String, String> column : columns) {
            String isConstantCol = column.get(IS_CONSTANT_COLUMN);
//            String name = column.get(FRONT_TABLE_NAME);
//            String targetName = column.get(TARGET_TABLE_NAME);
            String srcColName = column.get("srcColName");
            String tgtColName = column.get("tgtColName");
            String constantVal = column.get(CONSTANT_VALUE);
            //目标表必须包含来源表和来源ID这两个字段来保证数据的增量更新
            if ("来源表".equals(tgtColName)) {
                isConstantCol = "true";
                constantVal = frontTableName;
                //添加限制条件，对各个数据来源进行增量更新
                conditionSql += " AND ( (SELECT MAX(来源ID) FROM " + targetTableName + " where 来源表 = '" + frontTableName + "') IS NULL OR ID > (SELECT MAX(来源ID) FROM " + targetTableName + " where 来源表 = '" + frontTableName + "'))";
            }
            //处理静态数值列
            if (isConstantCol.equals("true") && null != constantVal) {
                sqlBuffer.append("'").append(constantVal).append("'");
                sqlBuffer.append(",");
            } else {
                if (null != srcColName) {
                    sqlBuffer.append(srcColName);
                    sqlBuffer.append(",");
                }
            }


        }
        sqlBuffer.deleteCharAt(sqlBuffer.length() - 1); // 删除末尾多余的逗号
        sqlBuffer.append(" FROM ").append(frontTableName).append(conditionSql);
        return sqlBuffer.toString();
    }

    public static String getDistinctColumSql(String tableName, List<DamengSourceColumn> columns, String distinctCol) {
        List<String> columnInOrder = new ArrayList<>();
        columnInOrder.add(distinctCol);
        for (DamengSourceColumn column : columns) {
            String colName = column.getName();
            if (!colName.equalsIgnoreCase(distinctCol)) {
                columnInOrder.add(colName);
            }
        }

        String columnInOrderStr = String.join(",", columnInOrder);
        String distinctSelect = " DISTINCT " + columnInOrderStr;
        return distinctSelect;
    }

    public static String getSelectDistinctColumsSql(String tableName, List<DamengSourceColumn> columns, String distinctCol) {

        List<String> columnInOrder = new ArrayList<>();
        columnInOrder.add(distinctCol);
        for (DamengSourceColumn column : columns) {
            String colName = column.getName();
            if (!colName.equalsIgnoreCase(distinctCol)) {
                columnInOrder.add(colName);
            }
        }

        StringBuffer insertDistinctBuf = new StringBuffer("INSERT INTO %s ");
        String columnInOrderStr = String.join(",", columnInOrder);
        String distinctSelect = String.format(" SELECT %s FROM %s ", columnInOrderStr, tableName);

        return distinctSelect;
    }

    public static String getSelectColumsSql(String tableName, List<DamengSourceColumn> columns) {
        String columnInOrderStr = getColumSql(columns);
        String selectSql = String.format(" SELECT %s FROM %s ", columnInOrderStr, tableName);
        //String insertDistinct = String.format("INSERT INTO %s ( %s ) ( %s )", this.currentTableName, columnInOrderStr, distinctSelect);

        return selectSql;
    }

    public static String getInsertOrderColumnSql(String currentTableName, String frontTableName, List<DamengSourceColumn> columns) {
        String selectSql = getSelectColumsSql(frontTableName, columns);
        String insertSql = String.format("INSERT INTO %s ( %s ) ( %s )", currentTableName, getColumSql(columns), selectSql);
        return insertSql;
    }

    public static String getInsertFullSql(String currenTable, String frontTable) {
        return String.format(" INSERT INTO %s ( SELECT * FROM %s ) ", currenTable, frontTable);
    }

    public static String getAggregateInsertSql(String frontTableName, String targetTableName, List<Map<String, String>> sourceColumnMap, List<String> targetColumnNames) {
        String selectAndStaticSql = getSelectAndStaticSql(sourceColumnMap, frontTableName, targetTableName);
        String insertSql = String.format("INSERT INTO %s ( %s ) ( %s )", targetTableName, String.join(",", targetColumnNames), selectAndStaticSql);
        return insertSql;
    }

    public static String getDecompositionInsertSql(String targetTableName, List<String> targetColumnNames, String selectSql) {
        String insertSql = String.format("INSERT INTO %s ( %s ) ( %s )", targetTableName, String.join(",", targetColumnNames), selectSql);
        return insertSql;
    }

    public static String getDropSchemaSql(String schema) {
        return String.format(" DROP SCHEMA IF EXISTS \"%s\" CASCADE; ", schema);
    }

}
